/*
* Copyrigth 2008 Company All rigths reserved
* File Name: TS_Preview_Result_CRUD.sql
* Created By:    Wenbin.Xu
* Created Date:   2013/7/8 16:27:28
* Description:    TS_Preview_Result的增删改查存储过程
* Change History: 
* 
* */

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Create  PROCEDURE UP_TS_Preview_Result_Add
	@PreviewId  uniqueidentifier,
	@UsreId  bigint,
	@QuestionId  uniqueidentifier,
	@Result  nvarchar(-1),
	@AnswerDate  datetime,
	@GroupId  int,
	@GroupDate  datetime,
	@Sort  int
AS
SET NOCOUNT ON
BEGIN
	INSERT INTO TS_Preview_Result
    (
        PreviewId,
        UsreId,
        QuestionId,
        Result,
        AnswerDate,
        GroupId,
        GroupDate,
        Sort
    )
	VALUES
    (
        @PreviewId,
        @UsreId,
        @QuestionId,
        @Result,
        @AnswerDate,
        @GroupId,
        @GroupDate,
        @Sort)
END





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROCEDURE UP_TS_Preview_Result_Delete
    @PreviewId  uniqueidentifier,
    @UsreId  bigint
AS
SET NOCOUNT ON
BEGIN
	DELETE FROM  TS_Preview_Result
	WHERE
    PreviewId=@PreviewId and 
    UsreId=@UsreId
END





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROCEDURE UP_TS_Preview_Result_Update
	@PreviewId  uniqueidentifier,
	@UsreId  bigint,
	@QuestionId  uniqueidentifier,
	@Result  nvarchar(-1),
	@AnswerDate  datetime,
	@GroupId  int,
	@GroupDate  datetime,
	@Sort  int
AS
SET NOCOUNT ON
BEGIN
	UPDATE TS_Preview_Result
    SET 
	QuestionId=@QuestionId,
	Result=@Result,
	AnswerDate=@AnswerDate,
	GroupId=@GroupId,
	GroupDate=@GroupDate,
	Sort=@Sort
    WHERE
    PreviewId=@PreviewId and 
    UsreId=@UsreId
END






set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROCEDURE UP_TS_Preview_Result_View
    @PreviewId  uniqueidentifier,
    @UsreId  bigint
AS
SET NOCOUNT ON
BEGIN
	SELECT 
    PreviewId,
    UsreId,
    QuestionId,
    Result,
    AnswerDate,
    GroupId,
    GroupDate,
    Sort
    FROM TS_Preview_Result with(nolock)
    WHERE 
    PreviewId=@PreviewId and 
    UsreId=@UsreId
END





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROCEDURE UP_TS_Preview_Result_ListByPaging
(
    @pageIndex  INT,
    @pageSize   INT ,
    @recordCount INT=0 OUTPUT 
)
AS
BEGIN
	
	--calculate start index & end index
	DECLARE @startIndex  AS INT,
	        @endIndex    AS INT
	
	SELECT @startIndex = @pageSize * (@pageIndex -1) + 1,
	       @endIndex = @pageSize * @pageIndex
	
	DECLARE @sql    NVARCHAR(max)
	DECLARE @where  NVARCHAR(2000)
	SET @sql=''	
	SET @where = ''
    
	
	--calculate total record count
	SET @sql='SELECT @recordCount=COUNT(1) FROM TS_Preview_Result with(nolock) WHERE (1=1) ' + @where
	EXEC sp_executesql @sql, N'@recordCount int=0 OUTPUT' , @recordCount OUTPUT 
	
	
	SET @sql = 
	    'SELECT ROW_NUMBER () OVER (ORDER BY 
            PreviewId ASC , 
            UsreId ASC
        ) RowId,
        PreviewId,
        UsreId,
        QuestionId,
        Result,
        AnswerDate,
        GroupId,
        GroupDate,
        Sort
        FROM TS_Preview_Result with(nolock)
        WHERE (1=1) ' + @where
				
	SET @sql = 'SELECT T.* FROM (' 
	+ @sql
	+ ') T where T.RowId between @startIndex and @endIndex '

	exec sp_executesql @sql,N'@startIndex int, @endIndex int',@startIndex,@endIndex 
END